package com.jyx.jdbc;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;


public class Jdbc {
	
	public static void main(String[] args) {
		
//		List<String> list = readFile();
//		for (String str : list) {
//			System.out.println(str);
//		}
		// derby();
		// getMetaDate();
		// createDocument();
		
	}
	
	public static void createDocument(){
		try {
			SAXReader reader = new SAXReader();
			Document doc = reader.read(new File("resource/user.xml"));
			Element root = doc.getRootElement();
			root.attribute("");
			Node node = root.getDocument();
			Document document = DocumentHelper.createDocument();
			document.setRootElement(root);
		} catch (DocumentException e) {
			e.printStackTrace();
		}
	}
	
	/** ResultMetaDate */
	public static void getMetaDate(){
		try {
			Properties prop = new Properties();
			prop.put("user", "user1");
			prop.put("password", "user1");
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			Connection conn = DriverManager.getConnection("jdbc:derby:helloDb;create=false",prop);
			Statement statement = conn.createStatement();
			ResultSetMetaData metaDate = statement.executeQuery("select name,score from helloTable").getMetaData();
			for (int i = 1; i <= metaDate.getColumnCount(); i++) {
				System.out.print("数据类型："+metaDate.getColumnClassName(i));
				System.out.print("数据库类型："+metaDate.getColumnTypeName(i));
				System.out.print("字段名称："+metaDate.getColumnName(i));
				System.out.print("字段长度："+metaDate.getColumnDisplaySize(i));
				System.out.println();
			}
			statement.close();
			conn.close();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/** jdk6自带上数据库derby */
	public static void derby(){
		try { 
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			Properties prop = new Properties();
			prop.put("user", "user1");
			prop.put("password", "user1");
			Connection coon;
			coon = DriverManager.getConnection("jdbc:derby:helloDb;create=false",prop);
			Statement statement = coon.createStatement();
			//statement.execute("create table helloTable(name varchar(50),age int)");
			//statement.execute("insert into helloTable values('黎明',50)");
			//statement.execute("insert into helloTable values('成龙m',65)");
			ResultSet rs = statement.executeQuery("select name,score from helloTable order by score desc");
			while(rs.next()){
				System.out.println(rs.getString("name")+"=============="+rs.getInt("score"));
			}
			rs.close();
			statement.close();
			coon.commit();
			coon.close();
			
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
        }
	}
	
	/** 读取文件*/
	public static List<String> readFile(){
		List<String> list = new ArrayList<String>();
		File file = new File("resource/user.xml");
		try {
			// 读取文件的所有内容
			FileReader fileReader = new FileReader(file);
			BufferedReader bufferReader = new BufferedReader(fileReader);
			while(bufferReader.ready()){
				String tel = bufferReader.readLine();
				if(tel.contains(" ")){
					tel = tel.replace(" ", "");
				}
				
				list.add(tel);
			}
			bufferReader.close();
			fileReader.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	private static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String URL = "jdbc:sqlserver://localhost:1433;DataBaseName=demo";

	private static String USER = "sa";
	private static String PASSWORD = "123@qwe";

	/**
	 *  获取连接
	 * @return
	 */
	protected Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 统计查询
	 * @param sql
	 * @param args
	 * @param clazz
	 * @return
	 */
	public int queryCount(String sql, List args, Class clazz) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(sql);
			// 如果sql语句有参数用args集合循环给其赋值
			if (args != null && args.size() > 0) {
				for (int i = 0; i < args.size(); i++) {
					pstmt.setObject(i + 1, args.get(i));// 参数位置号从1开始
				}
			}
			rs = pstmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(null, pstmt, conn);
		}
		return 0;

	}

	/**
	 *  查询方法
	 * @param sql
	 * @param args
	 * @param clazz
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List query(String sql, List args, Class clazz) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		// 存储返回的集合对象
		List list = new ArrayList();
		try {
			// 使用反射获取类的相关信息(实体类的属性--数据库里相应表的字段)
			Field[] fields = clazz.getDeclaredFields();
			Method method = null;

			conn = getConn();
			pstmt = conn.prepareStatement(sql);
			// 如果sql语句有参数用args集合循环给其赋值
			if (args != null && args.size() > 0) {
				for (int i = 0; i < args.size(); i++) {
					pstmt.setObject(i + 1, args.get(i));// 参数位置号从1开始
				}
			}
			rs = pstmt.executeQuery();
			// 获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();
			// 获取一共有多少列
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				// 创建一个新对象
				Object obj = clazz.newInstance();
				// 取出的结果集中列号从1开始
				for (int i = 1; i <= columnCount; i++) {
					String cname = rsmd.getColumnName(i);// 获取每一列的名称
					int ctype = rsmd.getColumnType(i);// 获取每一列的数据类型
					for (Field f : fields) {
						if (cname.equalsIgnoreCase(f.getName()))// 如果列名和字段名相同
						{
							// 开始封装数据
							String methodName = "set"
									+ f.getName().substring(0, 1).toUpperCase()
									+ f.getName().substring(1);

							// 根据列的type值的类型进行处理
							switch (ctype) {
							case Types.INTEGER:
								method = clazz.getMethod(methodName,
										Integer.class);
								method.invoke(obj, rs.getInt(i));
								break;
							case Types.VARCHAR:
								method = clazz.getMethod(methodName,
										String.class);
								method.invoke(obj, rs.getString(i));
								break;
							case Types.CHAR:
								method = clazz.getMethod(methodName,
										String.class);
								method.invoke(obj, rs.getString(i));
								break;

							// 前台的java数据类型为java.util.Date类型
							case Types.TIMESTAMP:
								method = clazz.getMethod(methodName,
										Timestamp.class);
								method.invoke(obj, rs.getString(i));
								break;
							case Types.DECIMAL:// 或者改为case
								// 3,因为数据库中的money类型的ctype值为3，注：java.math.bigdecimal对应数据库里的money类型
								method = clazz.getMethod(methodName,
										BigDecimal.class);
								method.invoke(obj, rs.getBigDecimal(i));
								break;
							}
						}
					}
				}
				list.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(null, pstmt, conn);
		}
		return list;
	}

	/**
	 *  执行增删改方法
	 * @param sql
	 * @param list
	 * @return
	 */
	public int saveOrDeleteOrUpdate(String sql, List list) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			// 循环给sql参数赋值
			if (list != null && list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {
					pstmt.setObject(i + 1, list.get(i));
				}
			}
			count = pstmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();// 捕获事务异常，事务回滚
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			System.out.println("数据库操作异常！");
		} finally {
			closeAll(null, pstmt, conn);
		}
		return count;
	}

	/**
	 *  关闭连接
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	protected void closeAll(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	/**
	 * 一个测试
	 * @param request
	 * @param response
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException
	 */
	public void one(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, IOException{
		Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
		String user="root";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		String action=request.getParameter("action");
		if(action!=null&&action.length()!=0)
		action=new String(action.getBytes("ISO-8859-1"));
		String studentname=request.getParameter("studentname");
		if(studentname!=null&&studentname.length()!=0)
		studentname=new String(studentname.getBytes("ISO-8859-1"),"utf-8");
		String matrino=request.getParameter("matrino");
		String sqlwhere=new String("");
		String sql=new String("");
		if("select".equals(action)){
			if(studentname!=null&&studentname.length()!=0)
				sqlwhere="where studentname like '%"+studentname+"%'";
			if(sqlwhere!=null&&sqlwhere.length()!=0){
				if(matrino!=null&&matrino.trim().length()!=0)
					sqlwhere=" and matrino like '%"+matrino.trim()+"%'";
			}else{
				if(matrino!=null&&matrino.trim().length()!=0)
					sqlwhere="where matrino like '%"+matrino.trim()+"%'";
			}
			sql="select* from student2 "+sqlwhere;
					
		}
		if("update".equals(action)){
			String studentcount=request.getParameter("studentcount");
			for(int i=1;i<=Integer.parseInt(studentcount);i++){
				String studentid=request.getParameter("studentid"+i);
				String classid=request.getParameter("classid"+i);
				if(classid!=null&&classid.length()!=0&&studentid!=null&&studentid.length()!=0){
					String sqlstr="update student2 set classid="+classid+" where studentid="+studentid;
					Statement state=conn.createStatement();
					state.executeUpdate(sqlstr);
				}
			}
			response.getWriter().print("设置分班成功!");
		}
		if("select".equals(action)){
			Statement state=conn.createStatement();
			ResultSet rs=state.executeQuery(sql);
			int i=0;
			while(rs.next()){
			i++;
			int zyid=rs.getInt("zyid");
		    if(zyid==0)
		    	response.getWriter().print("尚无专业");
			else
				sql="select * from zy where zyid="+zyid;
			
			Statement statetemp=conn.createStatement();
			ResultSet rstemp=statetemp.executeQuery(sql);
			if(rstemp.next())
				response.getWriter().print(rstemp.getString("zy"));
			else
				response.getWriter().print("尚无专业");
			
			int classid=rs.getInt("classid");
			sql="select * from classTa";
			statetemp=conn.createStatement();
			rstemp=statetemp.executeQuery(sql);
			while(rstemp.next()){
				int rstempclassid=rstemp.getInt("classid");
			}
		}
	}
	}
	
}
	
	
	
	
